package cn.com.libertymutual.sp.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.com.libertymutual.sp.bean.TbSpApplicant;

@Repository
public interface ApplicantDao extends PagingAndSortingRepository<TbSpApplicant, Integer>, JpaSpecificationExecutor<TbSpApplicant> {

	@Query(value = "SELECT * from tb_sp_applicant where TYPE = '2'and NAME like  %:name%", nativeQuery = true)
	List<TbSpApplicant> findByName(@Param("name") String name);

	@Query(value = "SELECT * from tb_sp_applicant where CAR_ID = :carId and TYPE = '2'", nativeQuery = true)
	List<TbSpApplicant> findByCarId(@Param("carId") String carId);

	@Query(value = "SELECT * from tb_sp_applicant where ORDER_NO = :orderNo and TYPE = '2' order by id", nativeQuery = true)
	List<TbSpApplicant> findAssuredByOrderNo(@Param("orderNo") String orderNo);

	@Query(value = "SELECT * from tb_sp_applicant where order_no = :orderNo and type = :type limit 1", nativeQuery = true)
	TbSpApplicant findByOrderNoAndType(@Param("orderNo") String orderNo, @Param("type") String type);

	@Query("from TbSpApplicant where orderNo = ?1")
	List<TbSpApplicant> findOrderNo(String orderNo);

	@Query("from TbSpApplicant where carId = ?1 and type =1  order by id desc")
	List<TbSpApplicant> findId(String carId);

	@Query(value = "SELECT * from tb_sp_applicant where ORDER_NO = :orderNo  order by id", nativeQuery = true)
	List<TbSpApplicant> findByOrderNo(@Param("orderNo") String orderNo);
}
